﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using StudentsManager.Models;
using StudentsManager.Exceptions;

namespace StudentsManager.DataAccess
{
    public class ClassDataAccess : DatabaseHelper
    {
        // 添加班级
        public void AddClass(Class cls)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                string query = "SELECT Id FROM Schools WHERE Name = @name";
                var schoolCommand = new SQLiteCommand(query, connection);
                schoolCommand.Parameters.AddWithValue("@name", cls.SchoolName);
                int schoolId = Convert.ToInt32(schoolCommand.ExecuteScalar());
                if (schoolId == 0)
                {
                    throw new NotExistException("学校不存在。");
                }

                var command = new SQLiteCommand("INSERT INTO Classes (Name, Description, SchoolId) VALUES (@name, @description, @schoolId)", connection);
                command.Parameters.AddWithValue("@name", cls.Name);
                command.Parameters.AddWithValue("@description", cls.Description);
                command.Parameters.AddWithValue("@schoolId", schoolId);
                command.ExecuteNonQuery();
            }
        }

        // 更新班级
        public void UpdateClass(Class cls)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                string query = "SELECT Id FROM Schools WHERE Name = @name";
                var schoolCommand = new SQLiteCommand(query, connection);
                schoolCommand.Parameters.AddWithValue("@name", cls.SchoolName);
                int schoolId = Convert.ToInt32(schoolCommand.ExecuteScalar());
                if (schoolId == 0)
                {
                    throw new NotExistException("学校不存在。");
                }

                var command = new SQLiteCommand("UPDATE Classes SET Name = @name, Description = @description, SchoolId = @schoolId WHERE Id = @id", connection);
                command.Parameters.AddWithValue("@name", cls.Name);
                command.Parameters.AddWithValue("@description", cls.Description);
                command.Parameters.AddWithValue("@schoolId", schoolId);
                command.Parameters.AddWithValue("@id", cls.Id);
                command.ExecuteNonQuery();
            }
        }

        // 删除班级（级联删除学生）
        public void DeleteClass(int classId)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                // 检查是否有学生与该班级相关联
                var studentCommand = new SQLiteCommand("SELECT COUNT(*) FROM Students WHERE ClassId = @classId", connection);
                studentCommand.Parameters.AddWithValue("@classId", classId);
                int studentCount = Convert.ToInt32(studentCommand.ExecuteScalar());

                if (studentCount > 0)
                {
                    throw new CascadeDeleteException("无法删除该班级，因为存在相关的学生记录。");
                }

                // 如果没有相关记录，执行删除操作
                var deleteCommand = new SQLiteCommand("DELETE FROM Classes WHERE Id = @id", connection);
                deleteCommand.Parameters.AddWithValue("@id", classId);
                deleteCommand.ExecuteNonQuery();
            }
        }

        // 查询所有班级
        public List<Class> GetClassList()
        {
            return GetClassList(null);
        }

        // 查询班级（根据schoolId）
        public List<Class> GetClassList(int schoolId)
        {
            return GetClassList(new Class { SchoolId = schoolId });
        }

        // 查询班级（根据class中的信息）
        public List<Class> GetClassList(Class cls)
        {
            List<Class> res = new List<Class>();
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                int schoolId = -1;

                if (cls != null && !string.IsNullOrEmpty(cls.SchoolName))
                {
                    var schoolCommand = new SQLiteCommand("SELECT Id FROM Schools WHERE Name = @name", connection);
                    schoolCommand.Parameters.AddWithValue("@name", cls.SchoolName);
                    schoolId = Convert.ToInt32(schoolCommand.ExecuteScalar());
                }

                if (cls.SchoolId != 0)
                {
                    schoolId = cls.SchoolId;
                }

                string query = "SELECT * FROM Classes WHERE 1=1";
                if (cls != null)
                {
                    if (!string.IsNullOrEmpty(cls.Name))
                    {
                        query += " AND Name LIKE @name";
                    }
                    if (!string.IsNullOrEmpty(cls.Description))
                    {
                        query += " AND Description LIKE @description";
                    }
                    if (schoolId != -1)
                    {
                        query += " AND SchoolId = @schoolId";
                    }
                }

                using (var command = new SQLiteCommand(query, connection))
                {
                    if (cls != null)
                    {
                        if (!string.IsNullOrEmpty(cls.Name))
                        {
                            command.Parameters.AddWithValue("@name", $"%{cls.Name}%");
                        }
                        if (!string.IsNullOrEmpty(cls.Description))
                        {
                            command.Parameters.AddWithValue("@description", $"%{cls.Description}%");
                        }
                        if (schoolId != -1)
                        {
                            command.Parameters.AddWithValue("@schoolId", schoolId);
                        }
                    }
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            res.Add(new Class
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                Description = reader["Description"].ToString(),
                                SchoolId = Convert.ToInt32(reader["SchoolId"])
                            });
                        }
                    }
                }
            }
            return res;
        }
    }
}
